const db = require("../db/index");

// 在头部区域导入 bcryptjs 后，
// 即可使用 bcrypt.compareSync(提交的密码，数据库中的密码) 方法验证密码是否正确
// compareSync() 函数的返回值为布尔值，true 表示密码正确，false 表示密码错误
const bcrypt = require("bcryptjs");
//----------------------------------------------------------------
//根据id删除用户的处理函数
exports.deleteUser = (req, res) => {
  Date.prototype.format = function (format) {
    var o = {
      "M+": this.getMonth() + 1, //month
      "d+": this.getDate(), //day
      "h+": this.getHours(), //hour
      "m+": this.getMinutes(), //minute
      "s+": this.getSeconds(), //second
      "q+": Math.floor((this.getMonth() + 3) / 3), //quarter
      S: this.getMilliseconds(), //millisecond
    };
    if (/(y+)/.test(format))
      format = format.replace(
        RegExp.$1,
        (this.getFullYear() + "").substr(4 - RegExp.$1.length)
      );
    for (var k in o)
      if (new RegExp("(" + k + ")").test(format))
        format = format.replace(
          RegExp.$1,
          RegExp.$1.length == 1
            ? o[k]
            : ("00" + o[k]).substr(("" + o[k]).length)
        );
    return format;
  };
  //----------------------------------------------------------------
  // console.log(req.body);
  var date_time = new Date().format("yyyy-MM-dd hh:mm");
  // console.log(date_time);
  const sql = `select floor,hostel from lab_users where id = ? `;
  db.query(sql, req.body.id, (err, results) => {
    if (err) return res.cc(err);
    // console.log(results);
    var floor = results[0].floor;
    // console.log(floor);
    var hostel = results[0].hostel;
    // console.log(date_time);
    const sql = `update lab_users set is_delete = 1 , end_time = ? where Id= ? `;
    db.query(sql, [date_time, req.body.id], (err, results) => {
      if (err) return res.cc(err);
      if (results.affectedRows !== 1) return res.cc("删除用户失败！");
      const sql = `update lab_class set residue = residue+1 where is_delete = 0 and lab_class_name = ? and lab_address = ? `;
      db.query(sql, [hostel, floor], (err, results) => {
        if (err) return res.cc(err);
        if (results.affectedRows !== 1) return res.cc("释放床铺失败！");
        res.cc("删除用户成功！", 0);
      });
    });
  });
};
//----------------------------------------------------------------
exports.deleteAdmin = (req, res) => {
  const id = req.body.id;
  const username = req.body.username;
  // console.log(id);
  // console.log(username);
  const sql = `select * from lab_device_cate where is_delete = 0 and admin_user = ? `;
  db.query(sql, username, (err, results) => {
    if (err) return res.cc(err);
    if (results.length > 0)
      return res.cc("此宿舍管理员已管理楼栋无法删除！请释放管理楼栋权限后重试");
    const sql = `update lab_users set is_delete = 1 where id = ? `;
    db.query(sql, id, (err, results) => {
      if (err) return res.cc(err);
      if (results.affectedRows !== 1) return res.cc("删除失败！");
      res.cc("删除用户成功！", 0);
    });
  });
};

//根据id审核用户的权限处理函数
exports.checkUser = (req, res) => {
  var access_power = null;
  var access_name = req.body.access_name;
  if (access_name == "学生") {
    access_power = 0;
  } else if (access_name == "宿舍管理员") {
    access_power = 2;
  }
  // const sql = `select * from lab_users where Id=?`;
  const sql = `update lab_users set access=? where Id=?`;
  db.query(sql, [access_power, req.body.id], (err, results) => {
    if (err) return res.cc(err);
    // console.log(results);
    if (results.affectedRows !== 1) return res.cc("审核用户权限失败！");
    res.cc("审核用户权限成功！", 0);
  });
};
//----------------------------------------------------------------
//根据id更新用户的信息处理函数
exports.updateUser = (req, res) => {
  req.body.password = bcrypt.hashSync(req.body.password, 10);
  // TODO：更新用户信息
  const sql = `update lab_users set ? where Id=?`;
  db.query(sql, [req.body, req.body.id], (err, results) => {
    // 执行 SQL 语句失败
    if (err) return res.cc(err);

    // SQL 语句执行成功，但是影响行数不等于 1
    if (results.affectedRows !== 1) return res.cc("更新用户信息失败！");

    // 更新用户信息成功
    res.cc("更新用户信息成功！", 0);
  });
};
//根据id获取用户的信息处理函数
exports.getUser = (req, res) => {
  // console.log(req.params);
  const sql = `select id , access_name,access,username, sno, college, aclass,phone_number,sex,floor,hostel from lab_users where id=?`;
  db.query(sql, req.params.id, (err, results) => {
    if (err) return res.cc(err);
    if (results.length !== 1) return res.cc("获取用户信息失败！");
    res.send({
      status: 0,
      message: "获取用户基本信息成功！",
      data: results[0],
    });
  });
};
//----------------------------------------------------------------
//获取宿舍管理员列表的函数
exports.getAdminList = (req, res) => {
  const sql = `select * from lab_users where access_name='宿舍管理员' and access=2 order by id desc `;
  db.query(sql, (err, results) => {
    // console.log(results);
    if (err) return res.cc(err);
    if (results.length == 0) return res.cc("没有相关信息！");
    res.send({
      status: 0,
      message: "获取用户基本信息成功！",
      data: results,
    });
  });
};
// ----------------------------------------------------------------
//获取所有宿舍管理员的信息处理函数
exports.getUsersAdmin = (req, res) => {
  var pagenum = req.body.pagenum; //页码值
  var pagesize = req.body.pagesize; //每页显示数据
  var username = req.body.username;
  var sno = req.body.sno;
  const count_value = (pagenum - 1) * pagesize; //查询的起始值
  // console.log(pagenum);
  // console.log(pagesize);
  // console.log(count_value);
  // console.log(username);
  // console.log(sno);
  var sql = null;
  if (username) {
    sql = `select * from lab_users where username like "%${req.body.username}%" and is_delete = 0 `;
  }
  if (sno) {
    sql = `select * from lab_users where sno like "${req.body.sno}%" and is_delete = 0 `;
  }
  if (username && sno) {
    sql = `select * from lab_users where username like "%${req.body.username}%" and sno like "${req.body.sno}%" and is_delete = 0 `;
  }
  if (!username && !sno) {
    sql = `select * from lab_users where access_name='宿舍管理员' and is_delete = 0 order by id desc `;
  }
  const countStr = `select count(*) as total from (${sql}) as usersinfo`;
  db.query(countStr, (err, results) => {
    if (err) return res.cc(err);
    const sum = results[0].total;
    // console.log(sum);
    sql += `limit ${count_value},${pagesize}`;
    db.query(sql, (err, results) => {
      if (err) return res.cc(err);
      if (results.length == 0) return res.cc("没有相关信息！");
      // console.log(results);
      res.send({
        status: 0,
        message: "获取用户基本信息成功！",
        data: results,
        total: sum,
      });
    });
  });
};
//----------------------------------------------------------------
//获取所有用户的信息处理函数
exports.getUsers = (req, res) => {
  var pagenum = req.body.pagenum; //页码值
  var pagesize = req.body.pagesize; //每页显示数据
  var username = req.body.username;
  var sno = req.body.sno;
  const count_value = (pagenum - 1) * pagesize; //查询的起始值
  // console.log(pagenum);
  // console.log(pagesize);
  // console.log(count_value);
  // console.log(username);
  // console.log(sno);
  var sql = null;
  if (username) {
    sql = `select * from lab_users where username like "%${req.body.username}%" and is_delete = 0 `;
  }
  if (sno) {
    sql = `select * from lab_users where sno like "${req.body.sno}%" and is_delete = 0 `;
  }
  if (username && sno) {
    sql = `select * from lab_users where username like "%${req.body.username}%" and sno like "${req.body.sno}%" and is_delete = 0 `;
  }
  if (!username && !sno) {
    sql = `select * from lab_users where access_name='学生' and is_delete = 0 order by id desc `;
  }
  const countStr = `select count(*) as total from (${sql}) as usersinfo`;
  db.query(countStr, (err, results) => {
    if (err) return res.cc(err);
    const sum = results[0].total;
    // console.log(sum);
    sql += `limit ${count_value},${pagesize}`;
    db.query(sql, (err, results) => {
      if (err) return res.cc(err);
      if (results.length == 0) return res.cc("没有相关信息！");
      // console.log(results);
      res.send({
        status: 0,
        message: "获取用户基本信息成功！",
        data: results,
        total: sum,
      });
    });
  });
};

//----------------------------------------------------------------
//获取所有历史用户的信息处理函数
exports.gethistoryUsers = (req, res) => {
  var pagenum = req.body.pagenum; //页码值
  var pagesize = req.body.pagesize; //每页显示数据
  var username = req.body.username;
  var sno = req.body.sno;
  const count_value = (pagenum - 1) * pagesize; //查询的起始值
  // console.log(pagenum);
  // console.log(pagesize);
  // console.log(count_value);
  // console.log(username);
  // console.log(sno);
  var sql = null;
  if (username) {
    sql = `select * from lab_users where username like "%${req.body.username}%" `;
  }
  if (sno) {
    sql = `select * from lab_users where sno like "${req.body.sno}%" `;
  }
  if (username && sno) {
    sql = `select * from lab_users where username like "%${req.body.username}%" and sno like "${req.body.sno}%" `;
  }
  if (!username && !sno) {
    sql = `select * from lab_users where access_name='学生' order by id desc `;
  }
  const countStr = `select count(*) as total from (${sql}) as usersinfo`;
  db.query(countStr, (err, results) => {
    if (err) return res.cc(err);
    const sum = results[0].total;
    // console.log(sum);
    sql += `limit ${count_value},${pagesize}`;
    db.query(sql, (err, results) => {
      if (err) return res.cc(err);
      if (results.length == 0) return res.cc("没有相关信息！");
      // console.log(results);
      res.send({
        status: 0,
        message: "获取用户基本信息成功！",
        data: results,
        total: sum,
      });
    });
  });
};
//----------------------------------------------------------------
// 获取用户基本信息的处理函数
exports.getUserInfo = (req, res) => {
  // 导入数据库操作模块

  // 根据用户的 id，查询用户的基本信息
  // 注意：为了防止用户的密码泄露，需要排除 password 字段
  const sql = `select id, username, sno,college,aclass, phone_number,email, user_pic ,hostel,floor from lab_users where id=?`;
  // 注意：req 对象上的 user 属性，是 Token 解析成功，express-jwt 中间件帮我们挂载上去的
  db.query(sql, req.user.id, (err, results) => {
    // 1. 执行 SQL 语句失败
    if (err) return res.cc(err);

    // 2. 执行 SQL 语句成功，但是查询到的数据条数不等于 1
    if (results.length !== 1) return res.cc("获取用户信息失败！");

    // 3. 将用户信息响应给客户端
    res.send({
      status: 0,
      message: "获取用户基本信息成功！",
      data: results[0],
    });
  });
};

// 更新用户基本信息的处理函数
exports.updateUserInfo = (req, res) => {
  // console.log(req.body);
  const sql = `update lab_users set ? where id=?`;
  db.query(sql, [req.body, req.body.id], (err, results) => {
    // 执行 SQL 语句失败
    if (err) return res.cc(err);

    // 执行 SQL 语句成功，但影响行数不为 1
    if (results.affectedRows !== 1) return res.cc("修改用户基本信息失败！");

    // 修改用户信息成功
    return res.cc("修改用户基本信息成功！", 0);
  });
};

// 重置密码的处理函数
exports.updatePassword = (req, res) => {
  // 定义根据 id 查询用户数据的 SQL 语句
  const sql = `select * from lab_users where id=?`;
  // 执行 SQL 语句查询用户是否存在
  db.query(sql, req.user.id, (err, results) => {
    // 执行 SQL 语句失败
    if (err) return res.cc(err);

    // 检查指定 id 的用户是否存在
    if (results.length !== 1) return res.cc("用户不存在！");

    // TODO：判断提交的旧密码是否正确
    // 判断提交的旧密码是否正确
    const compareResult = bcrypt.compareSync(
      req.body.oldPwd,
      results[0].password
    );
    if (!compareResult) return res.cc("原密码错误！");

    // 定义更新用户密码的 SQL 语句
    const sql = `update lab_users set password=? where id=?`;

    // 对新密码进行 bcrypt 加密处理
    const newPwd = bcrypt.hashSync(req.body.newPwd, 10);

    // 执行 SQL 语句，根据 id 更新用户的密码
    db.query(sql, [newPwd, req.user.id], (err, results) => {
      // SQL 语句执行失败
      if (err) return res.cc(err);

      // SQL 语句执行成功，但是影响行数不等于 1
      if (results.affectedRows !== 1) return res.cc("更新密码失败！");

      // 更新密码成功
      res.cc("更新密码成功！", 0);
    });
  });
};

// 更新用户头像的处理函数
exports.updateAvatar = (req, res) => {
  // console.log(req);
  const sql = "update lab_users set user_pic=? where id=?";
  db.query(sql, [req.body.avatar, req.user.id], (err, results) => {
    // 执行 SQL 语句失败
    if (err) return res.cc(err);

    // 执行 SQL 语句成功，但是影响行数不等于 1
    if (results.affectedRows !== 1) return res.cc("更新头像失败！");

    // 更新用户头像成功
    return res.cc("更新头像成功！", 0);
  });
};

// 获取宿舍名称列表的函数
exports.getHostelList = (req, res) => {
  const lab_address = req.params.dorm;
  const sql = `select lab_class_name from lab_class where is_delete = 0 and lab_address = ? `;
  db.query(sql, lab_address, (err, result) => {
    // console.log(result);
    if (err) return res.cc(err);
    // if (result.length == 0) return res.cc('无宿舍信息！')
    res.send({
      status: 0,
      message: "获取用户基本信息成功！",
      data: result,
    });
  });
};

// 入住登记的函数
exports.inHostel = (req, res) => {
  // console.log(req.body);
  // var data = req.body
  const hostel = req.body.hostel;
  const floor = req.body.floor;
  const sql = `update lab_class set residue=residue-1 where lab_class_name = ? and lab_address = ? and is_delete = 0`;
  db.query(sql, [hostel, floor], (err, results) => {
    if (err) {
      return res.cc("此宿舍已住满");
    }
    const sql = `update lab_users set ? where Id=?`;
    db.query(sql, [req.body, req.body.id], (err, result) => {
      // console.log(result);
      if (err) return res.cc(err);
      if (result.affectedRows !== 1) return res.cc("入住失败！");
      res.send({
        status: 0,
        message: "入住成功！",
      });
    });
  });
};

exports.cutHostel = (req, res) => {
  const data_lou = req.body.data_lou;
  const data_hostel = req.body.data_hostel;
  // console.log(data_lou);
  // console.log(data_hostel);

  const hostel = req.body.hostel;
  const id = req.body.id;
  const floor = req.body.floor;
  const data = {
    hostel: hostel,
    floor: floor,
  };
  const sql = `update lab_class set residue=residue-1 where lab_class_name = ? and lab_address = ? and is_delete = 0`;
  db.query(sql, [hostel, floor], (err, results) => {
    if (err) return res.cc("此宿舍已住满！");
    const sql = `update lab_class set residue=residue+1 where lab_class_name = ? and lab_address = ? and is_delete = 0`;
    db.query(sql, [data_hostel, data_lou], (err, results) => {
      if (err) return res.cc("失败");
      const sql = `update lab_users set ? where Id=?`;
      db.query(sql, [data, id], (err, result) => {
        // console.log(result);
        if (err) return res.cc(err);
        if (result.affectedRows !== 1) return res.cc("入住失败！");
        res.send({
          status: 0,
          message: "入住成功！",
        });
      });
    });
  });
};

exports.fix = (req, res) => {
  var id = req.params.id;
  // console.log(id);
  const sql = `update lab_users set fix_flag = 1 where id= ? `;
  db.query(sql, id, (err, results) => {
    if (err) return res.cc(err);
    if (results.affectedRows !== 1) return res.cc("维修失败！");
    res.send({
      status: 0,
      message: "维修中！",
    });
  });
};

exports.reFix = (req, res) => {
  var id = req.params.id;
  // console.log(id);
  const sql = `update lab_users set fix_flag = 0 where id= ? `;
  db.query(sql, id, (err, results) => {
    if (err) return res.cc(err);
    if (results.affectedRows !== 1) return res.cc("维修失败！");
    res.send({
      status: 0,
      message: "已维修成功！",
    });
  });
};

exports.visit = (req, res) => {
  var start_time = new Date();
  // var data = req.body
  data = {
    user_name: req.body.user_name,
    user_phone: req.body.user_phone,
    visit_name: req.body.visit_name,
    aim: req.body.aim,
    start_time: start_time,
  };
  // console.log(data);
  const sql = "insert into visit set ?";
  db.query(sql, data, (err, results) => {
    if (err) return res.cc(err);
    if (results.affectedRows !== 1) return res.cc("登记失败！");
    res.send({
      status: 0,
      message: "登记成功！",
    });
  });
};

exports.visitOut = (req, res) => {
  var id = req.params.id;
  // console.log(id);
  var end_time = new Date();
  var data = {
    end_time: end_time,
    flag: 1,
  };
  // console.log(id);
  const sql = `update visit set ? where id= ? `;
  db.query(sql, [data, id], (err, results) => {
    if (err) return res.cc(err);
    if (results.affectedRows !== 1) return res.cc("你走不了了！");
    res.send({
      status: 0,
      message: "已离开",
    });
  });
};

exports.visitAll = (req, res) => {
  var pagenum = req.body.pagenum; //页码值
  var pagesize = req.body.pagesize; //每页显示数据
  var username = req.body.username;
  const count_value = (pagenum - 1) * pagesize; //查询的起始值
  // console.log(pagenum);
  // console.log(pagesize);
  // console.log(count_value);
  // console.log(username);
  // console.log(sno);
  var sql = null;
  if (username) {
    sql = `select * from visit where user_name like "%${req.body.username}%" `;
  }
  if (!username) {
    sql = `select * from visit order by id desc `;
  }
  const countStr = `select count(*) as total from (${sql}) as usersinfo `;
  db.query(countStr, (err, results) => {
    if (err) return res.cc(err);
    const sum = results[0].total;
    // console.log(sum);
    sql += `limit ${count_value},${pagesize} `;
    db.query(sql, (err, results) => {
      // console.log(results);
      if (err) return res.cc(err);
      if (results.length == 0) return res.cc("没有相关信息！");
      // console.log(results);
      res.send({
        status: 0,
        message: "获取用户基本信息成功！",
        data: results,
        total: sum,
      });
    });
  });
};

exports.myHostel = (req, res) => {
  var sno = req.params.sno;
  // console.log(sno);
  const sql = `select * from lab_users where sno= ? and is_delete = 0 `;
  db.query(sql, sno, (err, results) => {
    // console.log(results);
    if (err) return res.cc(err);
    if (results.length == 0) return res.cc("你跑不了了....");
    var floor = results[0].floor;
    var hostel = results[0].hostel;
    // console.log(floor);
    // console.log(hostel);
    const sql = `select * from lab_users where floor = ? and hostel = ? and is_delete = 0 `;
    db.query(sql, [floor, hostel], (err, results) => {
      // console.log(results);
      if (err) return res.cc(err);
      if (results.length == 0) return res.cc("你跑不了了....");
      res.send({
        status: 0,
        message: "ok",
        data: results,
      });
    });
  });
};

// 获取全部通知
exports.getNotification = (req, res) => {
  var pagenum = req.body.pagenum; //页码值
  var pagesize = req.body.pagesize; //每页显示数据
  var text = req.body.text;
  var publish_time = req.body.publish_time;
  var status = req.body.status;
  //   console.log(status);

  const count_value = (pagenum - 1) * pagesize; //查询的起始值
  // console.log(pagenum);
  // console.log(pagesize);
  // console.log(count_value);
  // console.log(username);
  // console.log(sno);
  var sql = null;
  if (text) {
    sql = `select * from notification where text like "%${req.body.text}%" `;
  }
  if (publish_time) {
    sql = `select * from notification where publish_time like "%${req.body.publish_time}%" `;
  }
  if (status != null) {
    sql = `select * from notification where status like "%${req.body.status}%" `;
  }
  if (text && publish_time) {
    sql = `select * from notification where text like "%${req.body.text}%" and publish_time like "${req.body.publish_time}%" `;
  }
  if (!text && !publish_time && !status) {
    sql = `select * from notification order by id desc `;
  }
  const countStr = `select count(*) as total from (${sql}) as usersinfo`;
  db.query(countStr, (err, results) => {
    if (err) return res.cc(err);
    const sum = results[0].total;
    // console.log(sum);
    sql += `limit ${count_value},${pagesize}`;
    db.query(sql, (err, results) => {
      if (err) return res.cc(err);
      if (results.length == 0) return res.cc("没有相关信息！");
      // console.log(results);
      res.send({
        status: 0,
        message: "获取用户基本信息成功！",
        data: results,
        total: sum,
      });
    });
  });
};

//----------------------------------------------------------------
// 新增通知
exports.addNotification = (req, res) => {
  Date.prototype.format = function (format) {
    var o = {
      "M+": this.getMonth() + 1, //month
      "d+": this.getDate(), //day
      "h+": this.getHours(), //hour
      "m+": this.getMinutes(), //minute
      "s+": this.getSeconds(), //second
      "q+": Math.floor((this.getMonth() + 3) / 3), //quarter
      S: this.getMilliseconds(), //millisecond
    };
    if (/(y+)/.test(format))
      format = format.replace(
        RegExp.$1,
        (this.getFullYear() + "").substr(4 - RegExp.$1.length)
      );
    for (var k in o)
      if (new RegExp("(" + k + ")").test(format))
        format = format.replace(
          RegExp.$1,
          RegExp.$1.length == 1
            ? o[k]
            : ("00" + o[k]).substr(("" + o[k]).length)
        );
    return format;
  };
  // console.log(req.body);
  const publish_time = new Date().format("yyyy-MM-dd hh:mm");
  const text = req.body.text;
  const status_or = req.body.status_or;
  var data = {
    text: text,
    publish_time: publish_time,
    status_or: status_or,
  };
  //   console.log(data);
  const sql = `select * from notification where text = ? and status = 1`;
  db.query(sql, text, (err, results) => {
    if (err) return res.cc(err);
    if (results.length > 0) return res.cc("此通知重复，请更换重试！");
    // console.log(results);
    const sql = `insert into notification set ? `;
    db.query(sql, data, (err, results) => {
      if (err) return res.cc(err);
      if (results.affectedRows !== 1) return res.cc("新增失败！");
      res.cc("新增成功！", 0);
    });
  });
};

//----------------------------------------------------------------
// 根据id获取通知信息
exports.getNotificationByID = (req, res) => {
  const id = req.params.id;
  // console.log(id);
  const sql = `select * from notification where id = ? `;
  db.query(sql, id, (err, results) => {
    // console.log(results);
    if (err) return res.cc(err);
    if (results.length === 0) return res.cc("获取本信息失败！");

    res.send({
      status: 0,
      message: "获取信息成功！",
      data: results,
    });
  });
};

//--------------------------------
// 根据id修改通知信息
exports.editNotification = (req, res) => {
  const id = req.body.id;
  const text = req.body.text;
  const status = req.body.status;
  var data = {
    id: id,
    text: text,
    status: status,
  };
  // console.log(data);

  const sql = `select * from notification where id <> ? and text = ? and status = ? `;
  db.query(sql, [id, text, status], (err, results) => {
    // console.log(results);
    if (err) return res.cc(err);
    if (results.length === 1)
      return res.send({
        status: 1,
        message: "通知重复，请更换后重试！",
      });
    const sqll = `select text from notification where text = ? and status = ? `;
    db.query(sqll, [text, status], (err, results) => {
      if (err) return res.cc(err);
      if (results.length == 1) return res.cc("请更换重试！");
      const sql = `update notification set ? where id = ? `;
      db.query(sql, [data, id], (err, results) => {
        if (err) return res.cc(err);
        if (results.affectedRows !== 1) return res.cc("更新名称失败！");
        res.cc("更新成功！", 0);
      });
    });
  });
};

// ----------------------------------------------------------------
// 根据id删除
exports.deleteNotification = (req, res) => {
  const id = req.params.id;
  // console.log(id);
  const sql = `delete from notification where id = ? `;
  db.query(sql, id, (err, result) => {
    if (err) return res.cc(err);
    if (result.affectedRows !== 1) return res.cc("删除失败！");
    res.cc("删除成功！", 0);
  });
};

// ------------------------
// 获取全部意见
exports.getSuggestions = (req, res) => {
  var pagenum = req.body.pagenum; //页码值
  var pagesize = req.body.pagesize; //每页显示数据
  var title = req.body.title;
  var submit_time = req.body.submit_time;
  var status = req.body.status;
  //   console.log(status);

  const count_value = (pagenum - 1) * pagesize; //查询的起始值
  // console.log(pagenum);
  // console.log(pagesize);
  // console.log(count_value);
  // console.log(username);
  // console.log(sno);
  var sql = null;
  if (title) {
    sql = `select * from suggestions where title like "%${req.body.title}%" `;
  }
  if (submit_time) {
    sql = `select * from suggestions where submit_time like "%${req.body.submit_time}%" `;
  }
  if (status != null) {
    sql = `select * from suggestions where status like "%${req.body.status}%" `;
  }
  if (title && submit_time) {
    sql = `select * from suggestions where title like "%${req.body.title}%" and submit_time like "${req.body.submit_time}%" `;
  }
  if (!title && !submit_time && !status) {
    sql = `select * from suggestions order by id desc `;
  }
  const countStr = `select count(*) as total from (${sql}) as usersinfo`;
  db.query(countStr, (err, results) => {
    if (err) return res.cc(err);
    const sum = results[0].total;
    // console.log(sum);
    sql += `limit ${count_value},${pagesize}`;
    db.query(sql, (err, results) => {
      if (err) return res.cc(err);
      if (results.length == 0) return res.cc("没有相关信息！");
      // console.log(results);
      res.send({
        status: 0,
        message: "获取用户基本信息成功！",
        data: results,
        total: sum,
      });
    });
  });
};

//----------------------------------------------------------------
// 新增意见
exports.addSuggestions = (req, res) => {
  Date.prototype.format = function (format) {
    var o = {
      "M+": this.getMonth() + 1, //month
      "d+": this.getDate(), //day
      "h+": this.getHours(), //hour
      "m+": this.getMinutes(), //minute
      "s+": this.getSeconds(), //second
      "q+": Math.floor((this.getMonth() + 3) / 3), //quarter
      S: this.getMilliseconds(), //millisecond
    };
    if (/(y+)/.test(format))
      format = format.replace(
        RegExp.$1,
        (this.getFullYear() + "").substr(4 - RegExp.$1.length)
      );
    for (var k in o)
      if (new RegExp("(" + k + ")").test(format))
        format = format.replace(
          RegExp.$1,
          RegExp.$1.length == 1
            ? o[k]
            : ("00" + o[k]).substr(("" + o[k]).length)
        );
    return format;
  };
  //   console.log(req.body);
  const submit_time = new Date().format("yyyy-MM-dd hh:mm");
  const title = req.body.title;
  const content = req.body.content;
  const hostel = req.body.hostel;
  const sno = req.body.sno;
  const sno_name = req.body.sno_name;
  var data = {
    title: title,
    content: content,
    submit_time: submit_time,
    hostel,
    sno,
    sno_name,
  };
  // console.log(lab_address);
  const sql = `select * from suggestions where title = ? and status = 1 `;
  db.query(sql, title, (err, results) => {
    if (err) return res.cc(err);
    if (results.length > 0) return res.cc("重复，请更换重试！");
    // console.log(results);
    const sql = `insert into suggestions set ? `;
    db.query(sql, data, (err, results) => {
      if (err) return res.cc(err);
      if (results.affectedRows !== 1) return res.cc("新增失败！");
      res.cc("新增成功！", 0);
    });
  });
};

// 根据id获取通知信息
exports.getSuggestionsByID = (req, res) => {
  const id = req.params.id;
  // console.log(id);
  const sql = `select * from suggestions where id = ? `;
  db.query(sql, id, (err, results) => {
    // console.log(results);
    if (err) return res.cc(err);
    if (results.length === 0) return res.cc("获取本信息失败！");

    res.send({
      status: 0,
      message: "获取信息成功！",
      data: results,
    });
  });
};

// 根据id回复意见信息
exports.editSuggestions = (req, res) => {
  const id = req.body.id;
  const reply = req.body.reply;
  const status = 0;
  var data = {
    id: id,
    reply: reply,
    status,
  };
  const sqll = `select * from suggestions where id = ?`;
  db.query(sqll, id, (err, results) => {
    if (err) return res.cc(err);
    if (results.length == 0) return res.cc("请重试！");
    const sql = `update suggestions set ? where id = ? `;
    db.query(sql, [data, id], (err, results) => {
      if (err) return res.cc(err);
      if (results.affectedRows !== 1) return res.cc("失败！");
      res.cc("回复成功！", 0);
    });
  });
};

// 根据id报修
exports.editFix = (req, res) => {
  const path = require("path");
  // console.log(req.body) // 文本类型的数据
  // console.log('--------分割线----------')
  // console.log(req.file) // 文件类型的数据
  //   const sno = req.body.sno;
  //   const sno_name = req.body.sno_name;
  //   const device_serial = req.body.device_serial;
  //   const device_cate_id = req.body.device_cate_id;
  Date.prototype.format = function (format) {
    var o = {
      "M+": this.getMonth() + 1, //month
      "d+": this.getDate(), //day
      "h+": this.getHours(), //hour
      "m+": this.getMinutes(), //minute
      "s+": this.getSeconds(), //second
      "q+": Math.floor((this.getMonth() + 3) / 3), //quarter
      S: this.getMilliseconds(), //millisecond
    };
    if (/(y+)/.test(format))
      format = format.replace(
        RegExp.$1,
        (this.getFullYear() + "").substr(4 - RegExp.$1.length)
      );
    for (var k in o)
      if (new RegExp("(" + k + ")").test(format))
        format = format.replace(
          RegExp.$1,
          RegExp.$1.length == 1
            ? o[k]
            : ("00" + o[k]).substr(("" + o[k]).length)
        );
    return format;
  };
  const submit_time = new Date().format("yyyy-MM-dd hh:mm");
  const data = {
    ...req.body,
    status_type: 2,
    submit_time,
    picture: path.join("/uploads", req.file.filename),
  };
  const sql = `insert into suggestions set ? `;
  console.log(data);
  db.query(sql, data, (err, results) => {
    if (err) return res.cc(err);
    if (results.affectedRows !== 1) return res.cc("失败！");
    res.cc("成功！", 0);
  });
};

// 根据id回复意见信息
exports.editMark = (req, res) => {
  const id = req.body.id;
  const mark = req.body.mark;
  var data = {
    id: id,
    mark,
  };
  const sqll = `select * from suggestions where id = ?`;
  db.query(sqll, id, (err, results) => {
    if (err) return res.cc(err);
    if (results.length == 0) return res.cc("请重试！");
    const sql = `update suggestions set ? where id = ? `;
    db.query(sql, [data, id], (err, results) => {
      if (err) return res.cc(err);
      if (results.affectedRows !== 1) return res.cc("失败！");
      res.cc("成功！", 0);
    });
  });
};

// 根据id回复意见信息
exports.editSuggestionsFix = (req, res) => {
  const id = req.body.id;
  const reply = req.body.reply;
  var data = {
    id: id,
    reply: reply,
  };
  const sqll = `select * from suggestions where id = ?`;
  db.query(sqll, id, (err, results) => {
    if (err) return res.cc(err);
    if (results.length == 0) return res.cc("请重试！");
    const sql = `update suggestions set ? where id = ? `;
    db.query(sql, [data, id], (err, results) => {
      if (err) return res.cc(err);
      if (results.affectedRows !== 1) return res.cc("失败！");
      res.cc("回复成功！", 0);
    });
  });
};

// 根据id维修
exports.startFix = (req, res) => {
  const id = req.params.id;
  // console.log(id);
  const sql = `update suggestions set status_fix = 2 where id = ? and status_type = 2 `;
  db.query(sql, id, (err, result) => {
    if (err) return res.cc(err);
    if (result.affectedRows !== 1) return res.cc("失败！");
    res.cc("成功！", 0);
  });
};
// 根据id维修
exports.finishFix = (req, res) => {
  const id = req.params.id;
  // console.log(id);
  const sql = `update suggestions set status_fix = 3 where id = ? and status_type = 2 `;
  db.query(sql, id, (err, result) => {
    if (err) return res.cc(err);
    if (result.affectedRows !== 1) return res.cc("失败！");
    res.cc("成功！", 0);
  });
};

// 根据请假记录
exports.usersOff = (req, res) => {
  var data = {
    ...req.body,
  };
  console.log(data);

  const sql = `insert into lab_users_off set ? `;
  db.query(sql, data, (err, results) => {
    if (err) return res.cc(err);
    if (results.affectedRows !== 1) return res.cc("失败！");
    res.cc("回复成功！", 0);
  });
};

exports.getUsersOB = (req, res) => {
  var pagenum = req.body.pagenum; //页码值
  var pagesize = req.body.pagesize; //每页显示数据
  var username = req.body.username;
  var sno = req.body.sno;
  const count_value = (pagenum - 1) * pagesize; //查询的起始值
  var sql = null;
  if (username) {
    sql = `select * from lab_users_off where username like "%${req.body.username}%" `;
  }
  if (sno) {
    sql = `select * from lab_users_off where sno like "${req.body.sno}%" `;
  }
  if (username && sno) {
    sql = `select * from lab_users_off where username like "%${req.body.username}%" and sno like "${req.body.sno}%" `;
  }
  if (!username && !sno) {
    sql = `select * from lab_users_off order by id desc `;
  }
  const countStr = `select count(*) as total from (${sql}) as usersinfo`;
  db.query(countStr, (err, results) => {
    if (err) return res.cc(err);
    const sum = results[0].total;
    // console.log(sum);
    sql += `limit ${count_value},${pagesize}`;
    db.query(sql, (err, results) => {
      if (err) return res.cc(err);
      if (results.length == 0) return res.cc("没有相关信息！");
      // console.log(results);
      res.send({
        status: 0,
        message: "获取用户基本信息成功！",
        data: results,
        total: sum,
      });
    });
  });
};

// 新增退换宿
exports.addChangeHostel = (req, res) => {
  Date.prototype.format = function (format) {
    var o = {
      "M+": this.getMonth() + 1, //month
      "d+": this.getDate(), //day
      "h+": this.getHours(), //hour
      "m+": this.getMinutes(), //minute
      "s+": this.getSeconds(), //second
      "q+": Math.floor((this.getMonth() + 3) / 3), //quarter
      S: this.getMilliseconds(), //millisecond
    };
    if (/(y+)/.test(format))
      format = format.replace(
        RegExp.$1,
        (this.getFullYear() + "").substr(4 - RegExp.$1.length)
      );
    for (var k in o)
      if (new RegExp("(" + k + ")").test(format))
        format = format.replace(
          RegExp.$1,
          RegExp.$1.length == 1
            ? o[k]
            : ("00" + o[k]).substr(("" + o[k]).length)
        );
    return format;
  };
  //   console.log(req.body);
  const submit_time = new Date().format("yyyy-MM-dd hh:mm");
  const title = req.body.title;
  const content = req.body.content;
  const hostel = req.body.hostel;
  const sno = req.body.sno;
  const sno_name = req.body.sno_name;
  var data = {
    ...req.body,
    submit_time,
  };
  // console.log(lab_address);
  const sql = `select * from suggestions where title = ? and status = 1 `;
  db.query(sql, title, (err, results) => {
    if (err) return res.cc(err);
    if (results.length > 0) return res.cc("重复，请更换重试！");
    // console.log(results);
    const sql = `insert into suggestions set ? `;
    db.query(sql, data, (err, results) => {
      if (err) return res.cc(err);
      if (results.affectedRows !== 1) return res.cc("新增失败！");
      res.cc("新增成功！", 0);
    });
  });
};
